import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;

import org.apache.commons.io.output.FileWriterWithEncoding;
import org.w3c.dom.Document;
import org.w3c.dom.Element;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.SAXException;

import util.ModelUtil;

/**
 * @author oleksandr.bezhan
 * 
 *         May 12, 2012
 */
public class RecipesImporter {
    private static StringBuilder ingredients = new StringBuilder();
    private static Map<String, String[]> recipeImages = new HashMap<String, String[]>();
    private static StringBuilder recipes = new StringBuilder();
    private static int rowsDone;

    // private static Node getTableFromWorksheet(Node worksheet) {
    // Node table = worksheet.getChildNodes().item(1);
    // return table;
    // }

    private static String getTextFromCell(Node cellName) {
        return cellName.getFirstChild().getNodeValue();
    }

    // private static Node getWorksheetNode(Element root) {
    // NodeList childs = root.getChildNodes();
    // for (int i = 0; i < childs.getLength(); i++) {
    // Node element = childs.item(i);
    // if (element.getNodeName().equals("Worksheet")) {
    // return element;
    // }
    // }
    // return null;
    // }

    public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException,
                    ParserConfigurationException, SAXException {
        // open xml file
        File xmlFile = new File("tblMain.xml");
        if (!xmlFile.exists()) {
            throw new FileNotFoundException(xmlFile.getAbsolutePath());
        }

        // craete yml file
        File file = new File("conf/recipes.yml");
        if (file.exists()) {
            file.delete();
        }
        file.createNewFile();

        // document builder
        DocumentBuilder builder = DocumentBuilderFactory.newInstance().newDocumentBuilder();
        Document doc = builder.parse(xmlFile);
        Element root = doc.getDocumentElement();
        System.out.println("Root: " + root.getNodeName());
        NodeList childs = root.getChildNodes();
        for (int i = 0; i < childs.getLength(); i++) {
            Node child = childs.item(i);
            if (child.getNodeName().equals("tblMain")) {
                if (rowsDone == 0) {
                    // skip first row
                    rowsDone++;
                    continue;
                }
                readRow(child);
                // if (rowsDone == 2) {
                // break;
                // }
            }
        }
        rowsDone--;
        System.out.println("rowsdone: " + rowsDone);
        FileWriterWithEncoding writer = new FileWriterWithEncoding(file, "UTF-8");
        writer.append(recipes.toString());
        writer.flush();
        writer.close();

        // images
        // writer = new FileWriterWithEncoding(new File("conf/recipe-images"),
        // "UTF-8");
        // for (Entry<String, String[]> entry : recipeImages.entrySet()) {
        // writer.append(entry.getKey() + ":" + entry.getValue()[0]);
        // }
        // writer.flush();
        // writer.close();
    }

    private static void readRow(Node row) throws IOException {
        NodeList childs = row.getChildNodes();
        // allow only rows with description
        if (childs.getLength() != 29) {
            return;
        }
        // allow only ukrainian or russian recipes
        Node kuhnyaNode = childs.item(11);
        if (kuhnyaNode.getFirstChild() == null) {
            return;
        }
        String kuhnya = getTextFromCell(kuhnyaNode);
        if (!kuhnya.equals("Украинская кухня") && !kuhnya.equals("Русская кухня")) {
            return;
        }

        System.out.println("Row");
        // cell name
        Node cellName = childs.item(3);
        String name = getTextFromCell(cellName);
        String recipeName = name;
        String code = ModelUtil.translit(recipeName);
        String recipe = "Recipe(recipe_" + code + "):\r\n";
        recipe += " name: \"" + recipeName + "\"\r\n";
        // cell description
        Node item = childs.item(7);
        System.out.println("Descr: " + item);
        String description = getTextFromCell(item);
        recipe += " description: \"" + description + "\"\r\n";
        // cell picture
        Node cellPic = childs.item(25);
        String pic = getTextFromCell(cellPic);
        // save picture in public/images
        FileWriter writer = new FileWriter(new File("public/images/" + code + ".jpg"));
        writer.append(pic);
        writer.flush();
        writer.close();
        recipeImages.put(code, new String[] { code + ".jpg" });

        recipe += "\r\n";
        System.out.println(recipe);
        recipes.append(recipe);

        // cell ingredients
        // Node cellIngredients = childs.item(5);
        // if (cellIngredients != null) {
        // if (cellIngredients.getFirstChild() != null) {
        // String rawIngredients =
        // cellIngredients.getFirstChild().getNodeValue();
        // System.out.println("RawIngredients: " + rawIngredients);
        // String[] ingredients = rawIngredients.split(",");
        // for (String ingredient : ingredients) {
        // String ymlIngredient = "Ingredient():\r\n recipe: " + "recipe_" +
        // code + "\r\n product:";
        // }
        // }
        // }

        rowsDone++;
    }
}
